package com.soshalboard.utils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.dev.HSSF;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import com.soshalboard.config.ConfigHolder;

public class CreateExcel {
	/**
	 * @param args
	 */
	
	public HSSFWorkbook getExcelWorkBook(String excelRow0Schema, List<List> dataList, String row0merge, String row1Schema, String sheetName) {
		// TODO Auto-generated method stub
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet excelSheet = workbook.createSheet(sheetName);
	     excelSheet.setDefaultColumnWidth((short) 27);
	     generateSchema(excelRow0Schema, workbook, excelSheet, row0merge, row1Schema);
		//cell style
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 		
       /* Region rr = new Region();
        rr.setRowFrom(1);
        rr.setRowTo(1);
        rr.setColumnFrom((short) 2);
        rr.setColumnTo((short) 3);
        firstSheet.addMergedRegion(rr);*/
        int rowNum = 2;
        for(List<String> data : dataList)
    	{
           HSSFRow row = excelSheet.createRow(rowNum);
        	for(int i=0; i<data.size(); i++){
        		HSSFCell cell = row.createCell((short) i);
            	cell.setCellValue(data.get(i));
            	cell.setCellStyle(cellStyle);
        	}
        	rowNum++;
    	}
    	
        return workbook;
	}
	
	public void generateSchema(String excelRow0Schema, HSSFWorkbook workBook, HSSFSheet excelSheet, String row0merge, String row1Schema){
		//schema style
				HSSFCellStyle schemaStyle = workBook.createCellStyle();
				schemaStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				schemaStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				schemaStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
				schemaStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
				schemaStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				schemaStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
				schemaStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
				
				if (null != row0merge && !"".equals(row0merge)) {
					String[] excelRow0Merge = row0merge.split("#");
					
			        for(int i=0; i<excelRow0Merge.length; i++){
			        	Region region = new Region();
				        region.setRowFrom(0);
				        region.setRowTo(0);
			        	String[] mergeColumn = excelRow0Merge[i].split(",");
			        	region.setColumnFrom(Short.valueOf(mergeColumn[0]));
			        	region.setColumnTo(Short.valueOf(mergeColumn[1]));
			        	excelSheet.addMergedRegion(region);
			        }
				}
		        
		        HSSFRow row0 = excelSheet.createRow(0);
		        String[] row0Schema = excelRow0Schema.split(",");
		        for(int i=0; i<row0Schema.length; i++)
	        	{
	        		//System.out.println(dd.get(i));
	        		HSSFCell cell = row0.createCell((short) i);
	            	cell.setCellValue(row0Schema[i]);
	            	cell.setCellStyle(schemaStyle);
	        	}	
		        String[] excelRow1Schema = row1Schema.split(",");
		        HSSFRow row1 = excelSheet.createRow(1);
		        for(int j=0; j<excelRow1Schema.length; j++)
	        	{
	        		//System.out.println(dd.get(i));
	        		HSSFCell cell = row1.createCell((short) j);
	            	cell.setCellValue(excelRow1Schema[j]);
	            	cell.setCellStyle(schemaStyle);
	        	}
	}
}
